package com.bright.hxj.qchz.service;

import com.bright.ghj.common.util.DateUtil;
import com.bright.ghj.common.util.ListUtil;
import com.bright.hxj.qchz.mapper.CommonMapper;
import com.bright.hxj.qchz.pojo.bo.Duijie_ShiJieShouData;
import com.bright.hxj.qchz.pojo.bo.RoleInfo;
import com.bright.hxj.qchz.pojo.po.Duijie_ShiJieShouDeploy;
import com.bright.hxj.qchz.pojo.po.Duijie_ShiJieShouDeploy_TablePana;
import com.bright.hxj.qchz.pojo.po.Duijie_ShiJieShouUser;
import com.bright.hxj.qchz.util.ReplaceStringUtils;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.*;
/**
 * @Author hxj
 * @Date 2025/1/18 10:54
 * @Description
 */
@Component
@RequiredArgsConstructor
@Slf4j
public class Duijie_ShiJieShouCommonManager {

    private final Duijie_ShiJieShouMainKeyManager duijie_shiJieShouMainKeyManager;
    private final Duijie_ShiJieShouUserManager duijie_shiJieShouUserManager;
//    private final Duijie_ShiJieShouCommonManager duijie_shiJieShouCommonManager;
    private final LockManager lockManager;
    private final CommonMapper commonMapper;

    private final static String clientIdColumnName = "_clientSourceId";

    public RoleInfo insertOrUpdate_toDB(String userName, JSONObject jsonObject, String xmbm, Duijie_ShiJieShouDeploy deployOfMethod) {

        RoleInfo roleInfo = null;

        /*1.1、准备数据*/
        //主表信息

        String log_head = userName + "." + xmbm + "." + "dataId" + " ";

        Duijie_ShiJieShouUser user = duijie_shiJieShouUserManager.getUser(userName);

        final List<Duijie_ShiJieShouDeploy> deploys = Collections.singletonList(deployOfMethod);

//        if (deploys.size() == 0) {
//            roleInfo = new RoleInfo("请检查市平台的deploy，无法找到对应的记录！");
//            return roleInfo;
//        }

//        JdbcTemplateUtil jt = duijie_shi_jdbcTemplate;

        // 校验传过来的数据是否齐全，是否有遗漏某表的数据
        for (Duijie_ShiJieShouDeploy deploy : deploys) {
            if (deploy != null && !jsonObject.has(deploy.getType().toUpperCase())) {
                return new RoleInfo("请检查是否未传递" + deploy.getType() + "属性的数据！");
            }
        }

        //20221009 第三方编码替换 替换对接数据的ztid，distno
//        String dsfZtId = null;
//        String dsfDistNo = null;
//        List<Duijie_DiSanFangConfig> configList = duijie_diSanFangConfigManager.getDsfConfig(String.valueOf(jsonObject.get("userName")));
////        System.out.println("获取第三方信息，用户：" + jsonObject.get("userName") + "，获取mapList=" + mapList);
//        if (configList.size() > 0 && configList.get(0).getIs_dsf_code()) {
//            Duijie_DiSanFangConfig config = configList.get(0);
//            dsfZtId = config.getDsf_code_column();
//            dsfDistNo = config.getDsf_dist_column();
//            roleInfo = changeDistZtByDsfConfig(jsonObject, deploys, dsfZtId, dsfDistNo, config);
//
//            if (roleInfo != null) {
//                return roleInfo;
//            }
//        }


        //需要执行语句的参数
        Map<String, String> insert_sql = new LinkedHashMap<String, String>(); // key=deploy.type
        Map<String, String> update_sql = new LinkedHashMap<String, String>(); // key=deploy.type
        Map<String, String> delete_sql = new LinkedHashMap<String, String>(); // key=deploy.type
        Map<String, List<Map<String, Object>>> insert_parameters = new HashMap<>(); //key=deploy.type
        Map<String, List<Map<String, Object>>> update_parameters = new HashMap<>(); //key=deploy.type
        Map<String, List<List<Object>>> delete_parameters = new HashMap<>(); //key=deploy.type


        //获取到主键查询值及语句
        List<String> all_mainKeys = duijie_shiJieShouMainKeyManager.getColumns();
        // 20220726 hxj 分开每种子表不同主键 兼容变更表没有landNo、prj_no等的情况
        Map<String, StringBuffer> mainKey_sql_map = new HashMap<String, StringBuffer>(), mainKey_parameters_string_map = new HashMap<String, StringBuffer>();//key=deploy.type
//        Map<String, List<Object>> mainKey_parameters_map = new HashMap<String, List<Object>>(); // key=deploy.type
        StringBuffer mainDeploy_mainKey_sql = new StringBuffer(), mainDeploy_mainKey_parameters_string = new StringBuffer();// 主表的主键语句等 如ht_land的是landNo=?
//        List<Object> mainDeploy_mainKey_parameters = new ArrayList<Object>();// 主表的主键参数 如ht_land的是landNo
        // 20250118 bj-new整合修改 适配mybatisplus语法
        Map<String, Map<String, Object>> mainKey_parameters_map = new HashMap<>(); // key=deploy.type
        Map<String, Object> mainDeploy_mainKey_parameters = new HashMap<>();// 主表的主键参数 如ht_land的是landNo


        /*1.2、处理数据*/

        int i = 0;
        Duijie_ShiJieShouDeploy firstDeleteDeploy = null, firstInsertDeploy = null;
        Integer firstDeleteDeploy_deleteShiId = null;
        String firstInsertDeploy_insertClientId = null;
        Duijie_ShiJieShouDeploy mainDeploy = null; //主表配置
        Integer mainDeploy_tableId = null; //主表记录ID
        Map<String, Duijie_ShiJieShouDeploy> deploys_type = new HashMap<String, Duijie_ShiJieShouDeploy>();
        Map<Object, Duijie_ShiJieShouData> landInfoData_ids = null;  //待交易资产的ID、报价ID
        Map<Object, Duijie_ShiJieShouData> quotationData_ids = null;
        for(Duijie_ShiJieShouDeploy deploy: deploys) {
            i++;
            if (deploy == null) continue;
            //select

            deploys_type.put(deploy.getType().toUpperCase(), deploy);

            List<Duijie_ShiJieShouDeploy_TablePana> tablePanas = deploy.getTablePanas();

            //在业务表添加字段 存取第三方传递过来的原编码
//            if (configList.size() > 0 && configList.get(0).getIs_dsf_code()) {
//                tablePanas = getTablePanaWithDsfColumn(dsfZtId, dsfDistNo, deploy, tablePanas);
//            }

            //需要准备好的插入及更新数据
            String insSql = null, updSql = null, deleteSql = null;
            StringBuffer insertData_columnSql = new StringBuffer(), insertValueData_columnSql = new StringBuffer(), updateData_columnSql = new StringBuffer();
            List<Map<String, Object>> insertData = new ArrayList<>(), updateData = new ArrayList<>();
            List<List<Object>> deleteData = new ArrayList<>();

            if(i == 1 && !StringUtils.equalsIgnoreCase(deploy.getXmbm(), deploy.getType())) {
                roleInfo = new RoleInfo("请检测代码逻辑，获取的deploys的第一个应为xmbm=type。" + xmbm);
                break;
            }


            //获取表中的记录ID
            JSONArray client_data;
            Object typeObject = jsonObject.get(deploy.getType().toUpperCase());
            if(StringUtils.equalsIgnoreCase(deploy.getXmbm(), deploy.getType()) && typeObject instanceof JSONObject) {
                // 202220714 当是主表的数据时，只有一条数据
                // 20231019 省对接接收端州第三方数据的情况 主表允许传多条
                client_data = new JSONArray();
                JSONObject mainDeployDataObj = (JSONObject) typeObject;
                client_data.put(mainDeployDataObj);
            } else {
                client_data = (JSONArray) typeObject;
            }

            // 20241129 海珠 所有接口都进行字段映射
//            List<Duijie_DiSanFang_FieldMapping> dsfFieldMappings = getDsfFieldMapping(user.getUserName(), deploy.getType());
//            if (dsfFieldMappings.size() > 0) {
//                client_data = changeObjByDsfFieldMapping(client_data, dsfFieldMappings, jt);
//            }
            // 20220714 检查每条数据是否有传_clientSourceId
            for (int ii = 0; ii < client_data.length(); ii++) {
                JSONObject each_data = client_data.getJSONObject(ii);
                if (!each_data.has(clientIdColumnName)) {
                    roleInfo = new RoleInfo("请检查数据"+deploy.getType()+"(第"+(ii+1)+"行): 没有传主键字段"+clientIdColumnName);
                    break;
                }
                if (StringUtils.isEmpty(each_data.getString(clientIdColumnName))) {
                    roleInfo = new RoleInfo("请检查数据"+deploy.getType()+"(第"+(ii+1)+"行): 主键字段"+clientIdColumnName+"为空");
                    break;
                }
            }
            if (roleInfo != null) break;
            //获取主键
            // 20220726 hxj 分开每种子表单独定义主键
            StringBuffer mainKey_sql = new StringBuffer(), mainKey_parameters_string = new StringBuffer();
//            List<Object> mainKey_parameters = new ArrayList<Object>();\
            Map<String, Object> mainKey_parameters = new HashMap<>();
            // 20241204 hxj 第二个参数改成经dsf_field_mapping替换完字段名的数据包
            roleInfo = judgeMainKey(deploy, client_data, tablePanas, user, all_mainKeys, mainKey_sql, mainKey_parameters, mainKey_parameters_string, clientIdColumnName, mainDeploy_tableId, mainDeploy);
            if(roleInfo != null) return roleInfo;

            mainKey_sql_map.put(deploy.getType().toUpperCase(), mainKey_sql);
            mainKey_parameters_string_map.put(deploy.getType().toUpperCase(), mainKey_parameters_string);
            mainKey_parameters_map.put(deploy.getType().toUpperCase(), mainKey_parameters);
            // 获取主表参数等
            if(StringUtils.equalsIgnoreCase(deploy.getXmbm(), deploy.getType())) {
                mainDeploy = deploy;
                mainDeploy_mainKey_sql = mainKey_sql;
                mainDeploy_mainKey_parameters = mainKey_parameters;
                mainDeploy_mainKey_parameters_string = mainKey_parameters_string;

//                jt = getJdbcTemplateByDeploy(mainDeploy); // 20220816 hxj 获取主deploy的数据源
            }




            //获取表中的记录ID
            Map<Object, Duijie_ShiJieShouData> data_ids;
            try {
                data_ids = getZbfDataIds(clientIdColumnName, deploy, mainKey_sql, mainKey_parameters_string, mainKey_parameters, "zbfSourceId");
            } catch (Exception exception) {
                return new RoleInfo(exception.getMessage());
            }
//            mainDeploy_tableId = data_ids.get("mainDeploy_tableId").shiId; // 抽公共方法时，借用data_ids返回mainDeploy_tableId的值
            if (mainDeploy_tableId == null) {
                Integer tmpShiDataId = getShiDataId(deploy, mainKey_sql, mainKey_parameters);
                if (tmpShiDataId != null) {
                    mainDeploy_tableId = tmpShiDataId;
                }
            }
            if(StringUtils.equalsIgnoreCase(deploy.getType(), "land_info")) { //用于处理quotation和quotationDw
                landInfoData_ids = data_ids;
            }
            if(StringUtils.equalsIgnoreCase(deploy.getType(), "quotation") || StringUtils.equalsIgnoreCase(deploy.getType(), "jyq_quotation")) { //用于处理quotation和quotationDw
                quotationData_ids = data_ids;
            }


            //获取表中的记录ID （在上面获取了）
            List<String> client_dataIds = new ArrayList<String>();


            //根据客户端的数据生成要获取的数据
            boolean build_insertSql = false, build_updateSql = false; //是否已经生成的sql语句列字段
            for(int kk = 0; kk<client_data.length(); kk++) {
                JSONObject dataSub = client_data.getJSONObject(kk);

                String clientId = (String) dataSub.get(clientIdColumnName);
                client_dataIds.add(clientId);

                // 20220913 hxj 新会区数据不进行地区单
                //if (!StringUtils.equalsIgnoreCase(user.getUserName(), "jms_xhdsf") || !StringUtils.equalsIgnoreCase(user.getUserName(), "jms_hfdsf")) {
                // 地区单位校验
//                    String distNo = getValue(dataSub, deploy, "distNo");
//                    String ztId = getValue(dataSub, deploy, "ztId");
//                    roleInfo = judgeDistZtEmpty(jt, deploy, distNo, ztId, user);
//                    if (roleInfo != null) break;
//                }

                boolean isInsert = data_ids.get(clientId) == null; //是否为新增，否则为修改
                if (StringUtils.equalsIgnoreCase(deploy.getType(), deploy.getXmbm()) && isInsert) log.debug(deploy.getType()+"-------------isInsert 获取clientId: "+clientId+"为空，判断为需要新增");
                Duijie_ShiJieShouData shiData = data_ids.get(clientId);
                // 20220712 hxj 当属于新增的情况，data_ids.get(clientId)为空，下面会空指针
                Integer shi_dataId = null;
                if (!isInsert) {
                    shi_dataId = shiData.shiId; //市平台的ID
                }


                Map<String, Object> insertDataSub = new HashMap<>(), //新增执行语句的参数
                        updateDataSub = new HashMap<>();//修改执行语句的参数

//                boolean isExists = false;
//                int ii=-1;
                // 生成insertDataSub和updateDataSub的数据，并对数据进行校验
                roleInfo = getInsertAndUpdateData(user, all_mainKeys, deploy, tablePanas, insertData_columnSql, insertValueData_columnSql, updateData_columnSql, build_insertSql, build_updateSql, dataSub, isInsert, insertDataSub, updateDataSub, shi_dataId, clientIdColumnName);
                if(roleInfo != null) break;

                if(isInsert && !build_insertSql) build_insertSql = true;
                if(!isInsert && !build_updateSql) build_updateSql = true;

                if(isInsert) insertData.add(insertDataSub);
                if(!isInsert) updateData.add(updateDataSub);
            }
            if (roleInfo != null) break;

            //需要删除的
            for(Object shi_clientId: data_ids.keySet()) {
                if(client_dataIds.indexOf(shi_clientId) == -1) {
                    Duijie_ShiJieShouData shiData = data_ids.get(shi_clientId);
                    Integer shi_dataId = shiData.shiId; //data_ids.get(shi_clientId);

                    List<Object> deleteDataSub = new ArrayList<>(); //新增执行语句的参数
                    deleteDataSub.add(shi_dataId);
                    deleteData.add(deleteDataSub);
                }
            }

            Map<String, Object> insUpdDelSqlMap = getInsUpdDelSql(deploy, insertData, insertData_columnSql, insertValueData_columnSql, insert_sql, insert_parameters, updateData_columnSql, updateData, update_sql, update_parameters, deleteData, delete_sql, delete_parameters);
            if(firstInsertDeploy == null && insUpdDelSqlMap.containsKey("firstInsertDeploy")) {
                firstInsertDeploy = (Duijie_ShiJieShouDeploy) insUpdDelSqlMap.get("firstInsertDeploy");
                firstInsertDeploy_insertClientId = (String) insUpdDelSqlMap.get("firstInsertDeploy_insertClientId");
            }
            if(firstDeleteDeploy_deleteShiId == null && insUpdDelSqlMap.containsKey("firstDeleteDeploy_deleteShiId")) {
                firstDeleteDeploy_deleteShiId = (Integer) insUpdDelSqlMap.get("firstDeleteDeploy_deleteShiId");
                firstDeleteDeploy = (Duijie_ShiJieShouDeploy) insUpdDelSqlMap.get("firstDeleteDeploy");
            }
        }
        if(roleInfo != null) return roleInfo;

        //2、执行语句
        String lockName = "insertOrUpdate：" + xmbm + "." + mainDeploy_mainKey_parameters_string;
        synchronized (lockManager.getLock(lockName)) {
            try {
                //判断重复数据
                roleInfo = judgeShiDataRepeat(user, mainDeploy_mainKey_sql, mainDeploy_mainKey_parameters, firstDeleteDeploy, firstInsertDeploy, firstDeleteDeploy_deleteShiId, firstInsertDeploy_insertClientId, clientIdColumnName);


                if(roleInfo == null) {

                    final Map<String, List<Map<String, Object>>> insert_parameters_final = insert_parameters,
                            update_parameters_final = update_parameters;
                            final Map<String, List<List<Object>>> delete_parameters_final = delete_parameters;

                    // TODO 事务的问题看下怎么办 现在注入自己会报循环依赖
                    startOperatingDataBaseByTransaction(insert_parameters_final, update_parameters_final, delete_parameters_final, insert_sql, update_sql,
                            delete_sql, mainDeploy_tableId, landInfoData_ids, mainDeploy, mainDeploy_mainKey_sql, mainDeploy_mainKey_parameters, deploys_type,
                            mainKey_sql_map, mainKey_parameters_string_map, mainKey_parameters_map, quotationData_ids);
                }
            } catch (Exception e) {
                if (roleInfo == null) roleInfo = new RoleInfo(e.getMessage());

                log.error(log_head + "insertOrUpdate，异常：", e);
            }
//            } finally {
//                lockManager.releaseLock(lockName);
//            }
//        } else {
//            roleInfo = new RoleInfo("无法获取到锁。" + lockName);
        }

        if(roleInfo != null && StringUtils.equalsIgnoreCase(roleInfo.getLx(), "replaySubmit")) roleInfo = null;


        return roleInfo;
    }

    public Map<String, Object> getInsUpdDelSql(Duijie_ShiJieShouDeploy deploy,
                                               List<Map<String, Object>> insertData, StringBuffer insertData_columnSql, StringBuffer insertValueData_columnSql, Map<String, String> insert_sql, Map<String, List<Map<String, Object>>> insert_parameters, StringBuffer updateData_columnSql,
                                               List<Map<String, Object>> updateData, Map<String, String> update_sql, Map<String, List<Map<String, Object>>> update_parameters, List<List<Object>> deleteData, Map<String, String> delete_sql, Map<String, List<List<Object>>> delete_parameters) {

        // TODO 这里要适配MybatisPlus语法
        Map<String, Object> rvalue = new HashMap<String, Object>();
        String insSql, updSql, deleteSql;
        if(insertData.size() > 0) {
            insSql = "INSERT INTO " + deploy.getTableName() + "(" + insertData_columnSql.toString() + ", _clientUser, _clientDate) VALUES(" + insertValueData_columnSql.toString() + ", ?, ?)";
            insert_sql.put(deploy.getType().toUpperCase(), insSql);
            insert_parameters.put(deploy.getType().toUpperCase(), insertData);

//            if(firstInsertDeploy == null) {
//                firstInsertDeploy = deploy;
            rvalue.put("firstInsertDeploy", deploy);
//                firstInsertDeploy_insertClientId = (String) insertData.get(0).get(0);
            rvalue.put("firstInsertDeploy_insertClientId", insertData.get(0).get(deploy.getIdColumn().toUpperCase()));
//            }
        }
        if(updateData.size() > 0) {
            String extraColumnSql = ", _clientDate=?";
            // 20220811 hxj 财务公开主表 给旧数据更新_clientId和_clientUser
            if (StringUtils.equalsIgnoreCase(deploy.getType(), "openReportRecord")) {
                extraColumnSql += ", _clientUser=?";
            }
            updSql = "UPDATE " + deploy.getTableName() + " SET " + updateData_columnSql + extraColumnSql + " WHERE " + deploy.getIdColumn() + "=?";
            update_sql.put(deploy.getType().toUpperCase(), updSql);
            update_parameters.put(deploy.getType().toUpperCase(), updateData);
        }
        if(deleteData.size() > 0) {
            deleteSql = "DELETE " + deploy.getTableName() + " WHERE " + deploy.getIdColumn() + "=?";
            delete_sql.put(deploy.getType().toUpperCase(), deleteSql);
            delete_parameters.put(deploy.getType().toUpperCase(), deleteData);

//            if(firstDeleteDeploy_deleteShiId == null) {
//                firstDeleteDeploy_deleteShiId = (Integer) deleteData.get(0).get(0);
            rvalue.put("firstDeleteDeploy_deleteShiId", deleteData.get(0).get(0));
//                firstDeleteDeploy = deploy;
            rvalue.put("firstDeleteDeploy", deploy);
//            }
        }

//        rvalue.put("insSql", insSql);

        return rvalue;
    }

    public RoleInfo getInsertAndUpdateData(Duijie_ShiJieShouUser user, List<String> all_mainKeys, Duijie_ShiJieShouDeploy deploy, List<Duijie_ShiJieShouDeploy_TablePana> tablePanas, StringBuffer insertData_columnSql, StringBuffer insertValueData_columnSql, StringBuffer updateData_columnSql, boolean build_insertSql, boolean build_updateSql, Object dataSub, boolean isInsert,
                                           Map<String, Object> insertDataSub, Map<String, Object> updateDataSub, Integer shi_dataId, String clientIdColumnName) {
        boolean isExists;
        int ii=-1;
        RoleInfo roleInfo = null;
        Object clientId;
//        int dataSize; // 20221008 传递的字段个数，防止下标越界
        if (dataSub instanceof JSONArray) {
            clientId = ((JSONArray) dataSub).get(0);
//            dataSize = ((JSONArray) dataSub).size();
        } else if (dataSub instanceof  JSONObject) {
            clientId = ((JSONObject) dataSub).get(clientIdColumnName);
//            dataSize = tablePanas.size(); // 第三方不需要这个校验
        } else {
            return new RoleInfo("processInsertData: dataSub类型错误！既不是JSONArray也不是JSONObject");
        }

        String logHead = "对接用户" + user.getUserName() + " insertOrUpdate " + " " + clientId + "，";

        // 20221008 防止dataSub.get(ii)下标越界问题
//        if (tablePanas.size() != dataSize) {
//            roleInfo = new RoleInfo(deploy.getType() + "2.8.50数据传递字段个数不符，传递了" + dataSize + "，应为" + tablePanas.size());
//            logger.error(ReplaceStringUtils.vaildLog(logHead + "检验不通过，" + roleInfo.getNoRoleInfo()));
//            return roleInfo;
//        }
        for(Duijie_ShiJieShouDeploy_TablePana tablePana: tablePanas) {
            ii++;

            // String logHead = "对接用户" + user.getUserName() + " inserShitOrUpdate " + " " + clientId + "，";

            Object value = null;
            if (dataSub instanceof JSONArray) {
                value = ((JSONArray) dataSub).get(ii);
            } else {
                value = ((JSONObject) dataSub).get(tablePana.getTcolumn());
            }
//            Object value = dataSub.get(tablePana.getTcolumn());
            if(value instanceof JSONObject) {
                if(((JSONObject) value).isEmpty()) value = null;
            } else if(StringUtils.equalsIgnoreCase(tablePana.getTtype(), "N2") && value instanceof Integer) {
                value = BigDecimal.valueOf((Integer) value);
            } else if(StringUtils.equalsIgnoreCase(tablePana.getTtype(), "N2") && value instanceof Double) {
                value = BigDecimal.valueOf((Double) value);
            } else if(StringUtils.equalsIgnoreCase(tablePana.getTtype(), "D") && "".equals(value)) {
                value = null;
            }

            if(ii != 0) {
                if (isInsert && !build_insertSql) {
                    insertData_columnSql.append(", ");
                    insertValueData_columnSql.append(", ");
                } else if (!isInsert && !build_updateSql) {
                    updateData_columnSql.append(", ");
                }
            }

            //需要加前缀
            if(all_mainKeys.indexOf(tablePana.getTcolumn().toUpperCase()) != -1 && StringUtils.isNotEmpty((String) value)) {
                value = user.getHead() + value;
            }

            // 20220816 hxj 财务账套号acc_set各区需加上偏移值
            if (StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), "acc_set")) {
                if (user.getAcc_set_add() == null) return new RoleInfo(user.getUserName()+"用户未配置acc_set_add！");
                value = user.getAcc_set_add() + (Integer) value;
            }

            // 20231130 hxj 年报对接要允许ztid传空值 变更表也允许ztid传空值保证数据能正常对接 故去掉这里
//            if(StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), "ztId")) {
//                tablePana.setIsMust(true);
//            }

            // 20220811 hxj 财务按年月账号套对接的情况
            if (deploy.isHasDistNoColumn() && (StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), "years") || StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), "months") || StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), "zth"))) {
                tablePana.setIsMust(true);
            }

            if(value == null && tablePana.getIsMust()) {
                roleInfo = new RoleInfo(deploy.getType() + "2.8.7数据" + tablePana.getTcolumn() + "列为必填列，不能为空！");
                log.error(ReplaceStringUtils.vaildLog(logHead + "检验不通过，" + roleInfo.getNoRoleInfo()));
                isExists = true;
                break;
            }


            // 20220719 hxj 应用默认值
            String defaultValue = tablePana.getTdefault();
            if (value == null && StringUtils.isNotEmpty(defaultValue)) {

                if ((StringUtils.equalsIgnoreCase(tablePana.getTtype(), "C") ||
                        StringUtils.equalsIgnoreCase(tablePana.getTtype(), "D") ||
                        StringUtils.equalsIgnoreCase(tablePana.getTtype(), "M") ||
                        StringUtils.equalsIgnoreCase(tablePana.getTtype(), "MT")
                )) {
                    value = defaultValue;
                }
                if ((StringUtils.equalsIgnoreCase(tablePana.getTtype(), "N") || StringUtils.equalsIgnoreCase(tablePana.getTtype(), "N1"))) {
                    if (StringUtils.isEmpty(defaultValue)) value = 0;
                    else value = Integer.valueOf(defaultValue);
                }
                if (StringUtils.equalsIgnoreCase(tablePana.getTtype(), "N2")) {
                    if (StringUtils.isEmpty(defaultValue)) value = new BigDecimal("0.0");
                    else value = new BigDecimal(defaultValue);
                }
                if (StringUtils.equalsIgnoreCase(tablePana.getTtype(), "B")) {
                    value = Boolean.parseBoolean(defaultValue);
                }
            }

            if (value != null) {
                if ((StringUtils.equalsIgnoreCase(tablePana.getTtype(), "C") ||
                        StringUtils.equalsIgnoreCase(tablePana.getTtype(), "D") ||
                        StringUtils.equalsIgnoreCase(tablePana.getTtype(), "M") ||
                        StringUtils.equalsIgnoreCase(tablePana.getTtype(), "MT")
                ) && !(value instanceof String)) {
                    roleInfo = new RoleInfo(deploy.getType() + " 2.8.1数据" + tablePana.getTcolumn() + "列数需为String字符型，不可以是：" + value);
                    log.error(ReplaceStringUtils.vaildLog(logHead + "检验不通过，" + roleInfo.getNoRoleInfo()));
                    isExists = true;
                    break;
                }
                if ((StringUtils.equalsIgnoreCase(tablePana.getTtype(), "N1")) && !(value instanceof Integer)) {
                    roleInfo = new RoleInfo(deploy.getType() + " 2.8.2数据" + tablePana.getTcolumn() + "列数需为int数字型，不可以是：" + value);
                    log.error(ReplaceStringUtils.vaildLog(logHead + "检验不通过，" + roleInfo.getNoRoleInfo()));
                    isExists = true;
                    break;
                }
                if ((StringUtils.equalsIgnoreCase(tablePana.getTtype(), "N2")) && !(value instanceof Number)) {
                    roleInfo = new RoleInfo(deploy.getType() + " 2.8.3数据" + tablePana.getTcolumn() + "列数需为BigDecimal数字型，不可以是：" + value);
                    log.error(ReplaceStringUtils.vaildLog(logHead + "检验不通过，" + roleInfo.getNoRoleInfo()));
                    isExists = true;
                    break;
                }
                if ((StringUtils.equalsIgnoreCase(tablePana.getTtype(), "B")) && !(value instanceof Boolean)) {
                    roleInfo = new RoleInfo(deploy.getType() + " 2.8.4数据" + tablePana.getTcolumn() + "列数需为Boolean型，不可以是：" + value);
                    log.error(ReplaceStringUtils.vaildLog(logHead + "检验不通过，" + roleInfo.getNoRoleInfo()));
                    isExists = true;
                    break;
                }
                if ((StringUtils.equalsIgnoreCase(tablePana.getTtype(), "D")) && StringUtils.isNotEmpty((String) value) && !DateUtil.isDataStr((String) value)) {
                    roleInfo = new RoleInfo(deploy.getType() + " 2.8.4数据" + tablePana.getTcolumn() + "列数需为Date型，不可以是：" + value);
                    log.error(ReplaceStringUtils.vaildLog(logHead + "检验不通过，" + roleInfo.getNoRoleInfo()));
                    isExists = true;
                    break;
                }

                if ((value instanceof String && StringUtils.isEmpty((String) value) && tablePana.getIsMainKey()) ||
                        (value instanceof Integer && (Integer) value == 0 && tablePana.getIsMainKey()) ||
                        (value instanceof BigDecimal && ((BigDecimal) value).compareTo(BigDecimal.ZERO) == 0 && tablePana.getIsMainKey())
                ) {
                    roleInfo = new RoleInfo(deploy.getType() + " 2.8.5数据" + tablePana.getTcolumn() + "列为关键列，不能为空！");
                    log.error(ReplaceStringUtils.vaildLog(logHead + "检验不通过，" + roleInfo.getNoRoleInfo()));
                    isExists = true;
                    break;
                }

                if ((value instanceof String && StringUtils.isEmpty((String) value)) && tablePana.getIsMust()) {
                    roleInfo = new RoleInfo(deploy.getType() + " 2.8.6数据" + tablePana.getTcolumn() + "列为必填列，不能为空！");
                    log.error(ReplaceStringUtils.vaildLog(logHead + "检验不通过，" + roleInfo.getNoRoleInfo()));
                    isExists = true;
                    break;
                }
            }

            //判断是否在下拉列表
            if(tablePana.getIsList() && StringUtils.equalsIgnoreCase(tablePana.getTtype(), "C")) {
                List<List<Object>> listData = tablePana.getLists();
//                        String listSql = StringUtils.trimToEmpty(tablePana.getListSql());
//                        listSql = ReplaceStringUtils.getReplaceString(listSql, dist != null ? dist.getDistNo() : "",
//                                null, "0", user.getUserName(), DateUtil.getAllDate(DateUtil.getCurrDate()));
//                        if(StringUtils.isNotEmpty(listSql)) {
//                            listData = jdbcTemplate.queryForListList(listSql);
//                        }


                boolean isExistsList = false;
                StringBuilder list_desc = new StringBuilder();
                for(List<Object> listDataSub:listData) {
                    if(StringUtils.isNotEmpty(list_desc.toString())) list_desc.append("、");

                    list_desc.append(StringUtils.trimToEmpty((String)listDataSub.get(0)));

                    if (StringUtils.equalsIgnoreCase(StringUtils.trimToEmpty((String)listDataSub.get(0)), (String)value)) {
                        isExistsList = true;

                        value = (String)listDataSub.get(1);
                        break;
                    }
                }

                if(!isExistsList && !tablePana.getIsMust() && StringUtils.isEmpty((String)value)) {
                    isExistsList = true;
                }
                if(!isExistsList) {
                    roleInfo = new RoleInfo(deploy.getType() + " 2.8.7数据" + tablePana.getTcolumn() + "为下拉列表类型，值：" + value + "，不在规定的选项（" + list_desc + "）；");
                    log.error(ReplaceStringUtils.vaildLog(logHead + "检验不通过，" + roleInfo.getNoRoleInfo()));
                    isExists = true;
                    break;
                }
            }

            // 20220916 检验字段长度是否超长
            Integer maxLength = tablePana.getMaxLength();
            if (StringUtils.equalsIgnoreCase(tablePana.getTtype(), "C") && value != null && maxLength != null && maxLength > 0 && value.toString().length() > maxLength) {
                roleInfo = new RoleInfo(deploy.getType() + " 2.8.8数据" + tablePana.getTcolumn() + "字段超长，值：" + value + "，字节长度超过接口规定的" + maxLength);
            }


            if(roleInfo != null) break;


            //20220711 特殊处理：主表ID
            if(isNeedReplaceMainDeployId(deploy, tablePana)) {
                value = "${mainDeploy.tableId}";
            }
            if((StringUtils.equalsIgnoreCase(deploy.getType(), "quotation_dw") || StringUtils.equalsIgnoreCase(deploy.getType(), "jyq_quotation_dw")) && StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), "landInfoId")) {
//                        value = StringUtils.trimToEmpty((String) value) + "_${landInfo.id}";
                value = value + "_${landInfo.id}";
            }
            if((StringUtils.equalsIgnoreCase(deploy.getType(), "quotation") || StringUtils.equalsIgnoreCase(deploy.getType(), "jyq_quotation")) && (StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), "landInfoId") || StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), "bidUnitId"))) {
//                        value = StringUtils.trimToEmpty((String) value) + "_${landInfo.id}";
                value = value + "_${landInfo.id}";
            }
//            if((StringUtils.equalsIgnoreCase(deploy.getType(), "filelist_change_data_sub")) && StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), "landInfoId")) {
//                value = value + "_${landInfo.id}";
//            }

            if(isInsert) {
                insertDataSub.put(tablePana.getTcolumn(), value);

                if(!build_insertSql) {
                    insertData_columnSql.append(tablePana.getTcolumn());
                    insertValueData_columnSql.append("?");
                }
            } else {
                updateDataSub.put(tablePana.getTcolumn(), value);

                if(!build_updateSql) {
                    updateData_columnSql.append(tablePana.getTcolumn()).append("=?");
                }
            }


        }

        if(isInsert) {
            insertDataSub.put("_clientUser", user.getUserName());
            insertDataSub.put("_clientDate", new Timestamp(Calendar.getInstance().getTime().getTime()));
        }
        if(!isInsert) {
            updateDataSub.put("_clientDate", new Timestamp(Calendar.getInstance().getTime().getTime()));
            // 20220811 hxj 财务公开主表 给旧数据更新_clientId和_clientUser
            if (StringUtils.equalsIgnoreCase(deploy.getType(), "openReportRecord")) {
                updateDataSub.put("_clientUser", user.getUserName());
            }
            updateDataSub.put(clientIdColumnName, shi_dataId);
        }



        return roleInfo;
    }

    public Integer getShiDataId(Duijie_ShiJieShouDeploy deploy, StringBuffer mainKey_sql, Map<String, Object> mainKey_parameters) {
        Integer mainDeploy_tableId = null;
//        final List<List<Object>> tmpData = insert_parameters_final.get(key.toUpperCase());
//        //20220711 插入主表后获取主表ID
//        if(StringUtils.equalsIgnoreCase(key, mainDeploy.getType()) && tmpData.size() == 1) {

        // 20220727 hxj 兼容子表没有数据 又不必传的情况
        if (mainKey_sql.toString().isEmpty()) {
            return null;
        }

//        String sql = "SELECT " + deploy.getIdColumn() + " FROM " + deploy.getTableName() + " with(nolock) WHERE " + mainKey_sql.toString();
//        List<Map<String, Object>> maps = jt.queryForMapMap(sql, mainKey_parameters.toArray());
        List<Map<String, Object>> maps = commonMapper.selectData(deploy.getTableName(), deploy.getIdColumn(), mainKey_parameters, null);
        if(maps.size() > 0) {
//            System.out.println("---------------------------" + deploy.getType() + " getShiDataId 获取市平台id， maps=" + maps);
            mainDeploy_tableId = (Integer) maps.get(0).get(deploy.getIdColumn().toUpperCase());
        }
        return mainDeploy_tableId;
    }

    public RoleInfo judgeMainKey(Duijie_ShiJieShouDeploy deploy, JSONArray jsonArray, List<Duijie_ShiJieShouDeploy_TablePana> tablePanas, Duijie_ShiJieShouUser user,
                                 List<String> all_mainKeys, StringBuffer mainKey_sql, Map<String, Object> mainKey_parameters, StringBuffer mainKey_parameters_string, String clientIdColumnName, Integer mainDeploy_tableId, Duijie_ShiJieShouDeploy mainDeploy) {
        Object data; // 第一行数据
        if (jsonArray.length() == 0) {
            if (deploy.isMustHaveData()) {
                return new RoleInfo("无数据，请检查是否传递：" + deploy.getType());
            } else {
                return null;
            }
        }
        data = jsonArray.get(0);
        RoleInfo roleInfo = null;

        int j = -1;
        int mainKey_count = 0;

        String clientId;
        if (data instanceof JSONArray) {
            clientId = String.valueOf(((JSONArray) data).get(0));
        } else {
            clientId = String.valueOf(((JSONObject) data).get(clientIdColumnName));
        }

        // 变更表特殊处理
        if (StringUtils.contains(deploy.getType(), "filelist_change_data")) {
            Object shi_main_id = mainDeploy_tableId == null ? 0 : mainDeploy_tableId;
            getFilelistChangeMainKeyInfo(shi_main_id, mainDeploy, mainKey_sql, mainKey_parameters_string, mainKey_parameters, deploy);
            mainKey_count = 2; // 2个主键 changeName idValue/upload_key
        } else {

            for (Duijie_ShiJieShouDeploy_TablePana tablePana : tablePanas) {
                j++;

                if (tablePana.getIsMainKey()) {
                    Object value;// =  ((JSONArray) data.get(0)).get(j); //(String)
                    if (data instanceof JSONArray) {
                        value = ((JSONArray) data).get(j);
                    } else {
                        value = ((JSONObject) data).get(tablePana.getTcolumn());
                    }
                    if (value instanceof JSONObject) {
                        if (((JSONObject) value).isEmpty()) value = null;
                    }
                    String v = (value != null) ? String.valueOf(value) : null;
                    if (StringUtils.isEmpty(v)) {
                        roleInfo = new RoleInfo("关键词不能为空！" + deploy.getType() + "." + clientIdColumnName + "=" + clientId + ", " + tablePana.getTcolumn());
                    } else if (all_mainKeys.indexOf(tablePana.getTcolumn().toUpperCase()) != -1) {
                        v = user.getHead() + v;
                    } else if (StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), "acc_set")) {// 20220816 账套号偏移值 财务
                        v = String.valueOf(user.getAcc_set_add() + Integer.parseInt(v));
                    }


                    if (roleInfo == null && (StringUtils.equalsIgnoreCase(deploy.getType(), "ht_land") || StringUtils.equalsIgnoreCase(deploy.getType(), "ht_land_unit"))
                            && !StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), "landNo")) {
                        roleInfo = new RoleInfo("deploy：" + deploy.getType() + " 的主键应为landNo！");
                    }

                    if (roleInfo == null && (StringUtils.equalsIgnoreCase(deploy.getType(), "ht_contract") || StringUtils.equalsIgnoreCase(deploy.getType(), "ht_land_leaseRecords") ||
                            StringUtils.equalsIgnoreCase(deploy.getType(), "ht_contract_amount") || StringUtils.equalsIgnoreCase(deploy.getType(), "ht_contract_amount_detail_cs"))
                            && !StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), "cno")) {
                        roleInfo = new RoleInfo("deploy：" + deploy.getType() + " 的主键应为cno！");
                    }
                    // 20231031 hxj 为了允许交易使用交易编号作为主键来对接的情况 注释此段校验
//                    if (roleInfo == null && (StringUtils.equalsIgnoreCase(deploy.getType(), "proj") || StringUtils.equalsIgnoreCase(deploy.getType(), "land_info") ||
//                            StringUtils.equalsIgnoreCase(deploy.getType(), "quotation_dw") || StringUtils.equalsIgnoreCase(deploy.getType(), "quotation_dw") /*||
//                        StringUtils.equalsIgnoreCase(deploy.getType(), "policyinfo")*/) && !StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), "prj_no")) {
//                        roleInfo = new RoleInfo("deploy：" + deploy.getType() + " 的主键应为prj_no！");
//                    }
                    // 20221101 hxj 为了兼容自定义对接配置 对接表无唯一编号字段的情况
//                if (roleInfo == null && StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), clientIdColumnName)) {
//                    roleInfo = new RoleInfo("deploy：" + deploy.getType() + " 的主键不能配置为" + clientIdColumnName + "！");
//                }
                    if (roleInfo != null) return roleInfo;

                    // 20221020 hxj 变更表关联主表id的字段idValue和upload_key  转换为市的id
                    // 20221021 新增的情况也会进来 因为主表还未插入 mainDeploy_tableId为空 给0
                    if (isNeedReplaceMainDeployId(deploy, tablePana)) {
//                    System.out.println("--------------------------变更表替换主键，mainDeploy_tableId=" + mainDeploy_tableId);
                        v = String.valueOf(mainDeploy_tableId == null ? 0 : mainDeploy_tableId);
                    }

                    if (mainKey_count != 0) {
                        mainKey_sql.append(" AND ");
                        mainKey_parameters_string.append(",");
                    }
                    mainKey_sql.append(tablePana.getTcolumn()).append("=?");
                    mainKey_parameters.put(tablePana.getTcolumn(), v);
                    mainKey_parameters_string.append(tablePana.getTcolumn()).append("=").append(v);

                    // 20221103 hxj 对接公告表无唯一编号 使用_clientId和_clientUser作为主键 添加_clientUser
                    if (StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), clientIdColumnName)) {
                        mainKey_sql.append(" AND ").append("_clientUser").append("=?");
                        mainKey_parameters.put("_clientUser", user.getUserName());
                        mainKey_parameters_string.append(",").append("_clientUser").append("=").append(user.getUserName());
                        mainKey_count++;
                    }

                    mainKey_count++;
                }
            }

        }
//        } else {
//            roleInfo = new RoleInfo("无数据，请检查是否传递：" + deploy.getType());
//        }

        if(roleInfo == null && mainKey_count == 0) {
            roleInfo = new RoleInfo("无主键，请检查！应为landNo或cno等！");
        }
//        if(roleInfo != null) return roleInfo;
//        }
        return roleInfo;
    }

    private boolean isNeedReplaceMainDeployId(Duijie_ShiJieShouDeploy deploy, Duijie_ShiJieShouDeploy_TablePana tablePana) {
        return (StringUtils.equalsIgnoreCase(deploy.getType(), "filelist_change_data") || StringUtils.equalsIgnoreCase(deploy.getType(), "filelist_change_data_sub")) && (StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), "idValue") || StringUtils.equalsIgnoreCase(tablePana.getTcolumn(), "upload_key"));
    }

    private void getFilelistChangeMainKeyInfo(Object main_shi_id, Duijie_ShiJieShouDeploy mainDeploy,
            /*Map<String, StringBuffer> mainKey_sql_map, Map<String, StringBuffer> mainKey_parameters_string_map, Map<String, List<Object>> mainKey_parameters_map,*/
                                              StringBuffer mainKey_sql, StringBuffer mainKey_parameters_string, Map<String, Object> mainKey_parameters,
                                              Duijie_ShiJieShouDeploy deploy) {
        // 单独分开变更表的情况 主键取idValue changeName/upload_key changeName

        // 判断是主表还是子表 主表是idValue 子表是upload_key
        String idValueColumnName;
        if (StringUtils.equalsIgnoreCase(deploy.getType(), "filelist_change_data")) {
            idValueColumnName = "idValue";
        } else {
            idValueColumnName = "upload_key";
        }

        if (StringUtils.equalsIgnoreCase(mainDeploy.getType(), "proj")) {
            mainKey_parameters.put("changeName", "Transaction%");
        } else if (StringUtils.equalsIgnoreCase(mainDeploy.getType(), "ht_contract")) {
            mainKey_parameters.put("changeName", "Contract%");
        } else {
            mainKey_parameters.put("changeName", "Asset%");
        }
        mainKey_parameters.put(idValueColumnName, main_shi_id);

//        if (StringUtils.equalsIgnoreCase(deploy.getType(), "filelist_change_data")) {
//            mainKey_sql.append("changeName like ? and idValue = ?");
//            mainKey_parameters_string.append("changeName like ").append(mainKey_parameters.get(0)).append(" and idValue=").append(main_shi_id);
//        } else {
//            mainKey_sql.append("changeName like ? and upload_key = ?");
//            mainKey_parameters_string.append("changeName like ").append(mainKey_parameters.get(0)).append(" and upload_key=").append(main_shi_id);
//        }
        mainKey_parameters_string.append("changeName like ").append(mainKey_parameters.get("changeName")).append(" and ").append(idValueColumnName).append("=").append(main_shi_id);
    }

    public Map<Object, Duijie_ShiJieShouData> getZbfDataIds(String clientIdColumnName, Duijie_ShiJieShouDeploy deploy,
                                                            StringBuffer mainKey_sql, StringBuffer mainKey_parameters_string, Map<String, Object> mainKey_parameters, String zbfIdColumnName) throws Exception {
        Map<Object, Duijie_ShiJieShouData> data_ids = new HashMap<Object, Duijie_ShiJieShouData>(); //_clientId、id
//        data_ids.put("mainDeploy_tableId", new Duijie_ShiJieShouData(null, null, null));

        // 20220727 hxj 子表没有数据 获取不了主键sql的情况 返回无数据的map对象
        if (mainKey_sql.toString().isEmpty()) {
            return data_ids;
        }

        String columns = clientIdColumnName + ", " + deploy.getIdColumn();
        if (StringUtils.equalsIgnoreCase(deploy.getType(), "land_info")) {
            columns += ", "+zbfIdColumnName;
        } else {
            columns += ", '0' "+zbfIdColumnName;
        }


//        sql += " FROM " + deploy.getTableName() + " with(nolock) WHERE " + mainKey_sql.toString();
//        List<Map<String, Object>> maps = jt.queryForMapMap(sql, mainKey_parameters.toArray());
        List<Map<String, Object>> maps = commonMapper.selectData(deploy.getTableName(), columns, mainKey_parameters, null);
//        System.out.println("----------------------" + deploy.getType() + " getZbfDataIds查询，sql=" + sql + ", mainKey_parameters=" + mainKey_parameters);

        if(StringUtils.equalsIgnoreCase(deploy.getXmbm(), deploy.getType())) {
            if(maps.size() > 1 && !deploy.isMainTable_isMultiData()  // 允许主表在同一主键下有多条数据
                    && !StringUtils.equalsIgnoreCase(deploy.getXmbm(), "openReportRecord")) {// 20220811 财务公开主表数据多于1条
//                roleInfo = new RoleInfo("请检查市平台的数据是否重复。" + mainKey_parameters_string);
//                return roleInfo;
                throw new Exception("请检查市平台的数据是否重复。" + mainKey_parameters_string);
//            } else if(maps.size() == 1) {
//                // 借用data_ids返回mainDeploy_tableId值
//                data_ids.put("mainDeploy_tableId", new Duijie_ShiJieShouData(null, (Integer) maps.get(0).get(deploy.getIdColumn().toUpperCase()), null));
            }
        }
        for(Map<String, Object> map:maps) {
            if (map.get(clientIdColumnName.toUpperCase()) != null) {
                Duijie_ShiJieShouData shiData = new Duijie_ShiJieShouData(String.valueOf(map.get(clientIdColumnName.toUpperCase())),
                        (Integer) map.get(deploy.getIdColumn().toUpperCase()), String.valueOf(map.get(zbfIdColumnName.toUpperCase())));
                data_ids.put(String.valueOf(map.get(clientIdColumnName.toUpperCase())), shiData);
            }
        }
        return data_ids;
    }

    public RoleInfo judgeShiDataRepeat(Duijie_ShiJieShouUser user, StringBuffer mainKey_sql, Map<String, Object> mainKey_parameters, Duijie_ShiJieShouDeploy firstDeleteDeploy, Duijie_ShiJieShouDeploy firstInsertDeploy, Integer firstDeleteDeploy_deleteShiId, Object firstInsertDeploy_insertClientId, String clientIdColumnName) {
        RoleInfo roleInfo = null;
        if(firstInsertDeploy != null && !StringUtils.equalsIgnoreCase(firstInsertDeploy.getType(), "openReportRecord")) {// 20230620 财务公开表不判断 open表一次传递一个单位下的多条数据
//            JdbcTemplateUtil jt = getJdbcTemplateByDeploy(firstInsertDeploy);
            if(StringUtils.equalsIgnoreCase(firstInsertDeploy.getXmbm(), firstInsertDeploy.getType())) {
//                String sql = "SELECT " + clientIdColumnName + " FROM " + firstInsertDeploy.getTableName() + " WHERE " + mainKey_sql;
//                List<List<Object>> lists = jt.queryForListList(sql, mainKey_parameters.toArray());
                List<Map<String, Object>> maps = commonMapper.selectData(firstInsertDeploy.getTableName(), clientIdColumnName, mainKey_parameters, null);
                if (maps.size() > 0) {
                    Object existedClientId = maps.get(0).get(clientIdColumnName);
                    if (existedClientId == null) {
                        roleInfo = new RoleInfo(firstInsertDeploy.getType() + " 2.9.1 编号：" + mainKey_parameters + "的记录已在市平台存在但" + clientIdColumnName + "为空，请删掉市平台" + clientIdColumnName + "为空的数据再对接！");
                    } else {
                        // 20221103 hxj 如果对接重复的主键编号的数据，也会被判断为新增重复提交，这里改成报错返回回去
                        roleInfo = new RoleInfo(firstInsertDeploy.getType() + " 2.9.2 编号：" + mainKey_parameters + "已被对接过，已存在记录的" + clientIdColumnName + "：" + existedClientId + "，请检查编号是否重复！");
                    }
                    roleInfo.setLx("mainKeyRepeat"); // 20221103 更新了lx名称，不会被忽略
//                    logger.warn("判断新增重复提交！mainKey_sql=" + mainKey_sql + ", mainKey_parameters=" + mainKey_parameters);
                }

                // 20230318 再校验一下_clientUser和_clientId
//                sql = "SELECT " + firstInsertDeploy.getIdColumn() + " FROM " + firstInsertDeploy.getTableName() + " WHERE _clientUser=? AND " + clientIdColumnName + "=?";
//                lists = jt.queryForListList(sql, new Object[]{user.getUserName(), firstInsertDeploy_insertClientId});
                Map<String, Object> params = new HashMap<>(2);
                params.put("_clientUser", user.getUserName());
                params.put(clientIdColumnName, firstInsertDeploy_insertClientId);
                maps = commonMapper.selectData(firstInsertDeploy.getTableName(), firstDeleteDeploy.getIdColumn(), params, null);
                if (maps.size() > 0) {
                    // 20230318 hxj 主键内容变更的情况 将删除市平台的数据 再继续对接
//                    duijie_shi_jdbcTemplate.update("delete from " + firstInsertDeploy.getTableName() + " WHERE _clientUser=? AND " + clientIdColumnName + "=?", new Object[]{user.getUserName(), firstInsertDeploy_insertClientId});
                    commonMapper.deleteData(firstInsertDeploy.getTableName(), params);
                    log.warn("主表主键内容疑似发生变动！(不返回错误信息)" + firstInsertDeploy.getType() + " 编号：" + mainKey_parameters + "，已自动删除_clientUser=" + user.getUserName() + ", _clientId="+firstInsertDeploy_insertClientId);
                }

            } else { //子表使用_clientUser和_clientId
                // 20230404 获取主键字段内容
                String mainKeyColumn = firstInsertDeploy.getMainKeys().size() == 0 ? "" : "," + firstInsertDeploy.getMainKeys().get(0) + " as mainKeyColumn", mainKeyContent = null;
                Object clientDate = null;

//                String sql = "SELECT " + firstInsertDeploy.getIdColumn() + mainKeyColumn + ", _clientDate FROM " + firstInsertDeploy.getTableName() + " WHERE _clientUser=? AND " + clientIdColumnName + "=?";
//                if (StringUtils.equalsIgnoreCase(user.getUserName(), "gzs_by")) {
//                    logger.info(sql);
//                    logger.info(Arrays.asList(user.getUserName(), firstInsertDeploy_insertClientId));
//                }
//                List<List<Object>> lists = jt.queryForListList(sql, new Object[]{user.getUserName(), firstInsertDeploy_insertClientId});
//                System.out.println(lists);
                Map<String, Object> params = new HashMap<>(2);
                params.put("_clientUser", user.getUserName());
                params.put(clientIdColumnName, firstInsertDeploy_insertClientId);
                List<Map<String, Object>> maps = commonMapper.selectData(firstInsertDeploy.getTableName(), firstInsertDeploy.getIdColumn() + mainKeyColumn + ", _clientDate as _clientDate", params, null);
                if (maps.size() > 0) {
                    // 20221103 hxj 这段不用返回报错 子表判断重复提交是用_clientId和_clientUser判断的，不存在编号重复情况
//                    roleInfo = new RoleInfo("新增重复提交，跳过！");
//                    roleInfo.setLx("replaySubmit");
                    // 20230228 hxj 这段也要返回报错 否则数据不被更新也不报错造成数据有误
                    // 20230303 hxj 市平台一侧自动删除这条异常数据 让区再重试 再有再删
//                    duijie_shi_jdbcTemplate.update("delete from " + firstInsertDeploy.getTableName() + " WHERE _clientUser=? AND " + clientIdColumnName + "=?", new Object[]{user.getUserName(), firstInsertDeploy_insertClientId});
                    commonMapper.deleteData(firstInsertDeploy.getTableName(), params);

                    // 20230404 获取旧主键内容
                    if (StringUtils.isNotEmpty(mainKeyColumn)) {
                        mainKeyContent = (String) maps.get(0).get("mainKeyColumn");
                        clientDate = maps.get(0).get("_clientDate");
                    }

                    roleInfo = new RoleInfo(firstInsertDeploy.getType() + " 2.9.3 " + clientIdColumnName + "：" + firstInsertDeploy_insertClientId + "的记录已在市平台存在但主键字段内容跟区不匹配！原主键：" + mainKeyContent + ", 新主键：" + mainKey_parameters + ", 上次对接时间" + clientDate + ", 数据在市平台已自动删除，请重试！");
                    roleInfo.setLx("mainKeyError"); // 20230228 更新了lx名称，不会被忽略
                    log.warn("子表主键内容异常！" + firstInsertDeploy.getType() + " 2.9.3 编号：" + mainKey_parameters + "的记录已在市平台存在但主键字段内容跟区不匹配！已自动删除，_clientUser=" + user.getUserName() + ", _clientId="+firstInsertDeploy_insertClientId+"原主键内容：" + mainKeyContent + ", 上次对接时间" + clientDate);
                }
            }
        }
        if(roleInfo == null && firstDeleteDeploy_deleteShiId != null) {
            roleInfo = judgeShiDataDeleteRepeat(firstDeleteDeploy, firstDeleteDeploy_deleteShiId, user);
        }
        return roleInfo;
    }

    public RoleInfo judgeShiDataRepeat(Duijie_ShiJieShouUser user, StringBuffer mainKey_sql, Map<String, Object> mainKey_parameters, Duijie_ShiJieShouDeploy firstDeleteDeploy, Duijie_ShiJieShouDeploy firstInsertDeploy, Integer firstDeleteDeploy_deleteShiId, Object firstInsertDeploy_insertClientId) {
        return judgeShiDataRepeat(user, mainKey_sql, mainKey_parameters, firstDeleteDeploy, firstInsertDeploy, firstDeleteDeploy_deleteShiId, firstInsertDeploy_insertClientId, "_clientId");
    }

    public RoleInfo judgeShiDataDeleteRepeat(Duijie_ShiJieShouDeploy deploy, Integer deleteShiId, Duijie_ShiJieShouUser user) {
        // 校验删除重复提交
        RoleInfo roleInfo = null;
        if(deploy != null) {
//            JdbcTemplateUtil jt = getJdbcTemplateByDeploy(deploy);
//            String sql = "SELECT " + deploy.getIdColumn() + " FROM " + deploy.getTableName() + " WHERE " + deploy.getIdColumn() + "=?";
            List<Map<String, Object>> maps = commonMapper.selectData(deploy.getTableName(), deploy.getIdColumn(), Collections.singletonMap(deploy.getIdColumn(), deleteShiId), null);
            if(maps.size() == 0) {
                roleInfo = new RoleInfo("删除重复提交，跳过！");
                roleInfo.setLx("replaySubmit");
                System.out.println("判断删除重复提交，跳过！deleteShiId=" + deleteShiId);
            }
        }
        return roleInfo;
    }


    @Transactional
    public void startOperatingDataBaseByTransaction(Map<String, List<Map<String, Object>>> insert_parameters_final, Map<String, List<Map<String, Object>>> update_parameters_final,
                                                    Map<String, List<List<Object>>> delete_parameters_final, Map<String, String> insert_sql, Map<String, String> update_sql,
                                                    Map<String, String> delete_sql, Integer mainDeploy_tableId, Map<Object, Duijie_ShiJieShouData> landInfoData_ids,
                                                    Duijie_ShiJieShouDeploy mainDeploy, StringBuffer mainDeploy_mainKey_sql, Map<String, Object> mainDeploy_mainKey_parameters,
                                                    Map<String, Duijie_ShiJieShouDeploy> deploys_type, Map<String, StringBuffer> mainKey_sql_map, Map<String, StringBuffer> mainKey_parameters_string_map,
                                                    Map<String, Map<String, Object>> mainKey_parameters_map, Map<Object, Duijie_ShiJieShouData> quotationData_ids) throws Exception {
        //spring transaction begin
        //启用事务，处理关键字段重复问题，事务不含onSaveEndttt
//        DataSourceTransactionManager transactionManager = (DataSourceTransactionManager) SpringInit.getApplicationContext().getBean("duijie_shi_transactionManager");
//        DefaultTransactionDefinition def = new DefaultTransactionDefinition();
//        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED); // 事物隔离级别，开启新事务
//        def.setTimeout(5);
//        TransactionStatus status = transactionManager.getTransaction(def); // 获得事务状态

        String sql = null;
        List<List<Object>> data = null;
        boolean isException = false;
        Exception exception = null;

//        try {
            //新增
            Map<String, Map<Object, Duijie_ShiJieShouData>> xmbm_dataIds = new HashMap<String, Map<Object, Duijie_ShiJieShouData>>(); //key = deploy.type



            for(final String key: insert_sql.keySet()) {
                sql = insert_sql.get(key);

                final Integer mainDeploy_tableId_final = mainDeploy_tableId;

                //待交易资产的数据
                Map<Object, Duijie_ShiJieShouData> landInfoData_ids_22 = landInfoData_ids;
                if(xmbm_dataIds.get("land_info".toUpperCase()) != null) {
                    landInfoData_ids_22 = xmbm_dataIds.get("land_info".toUpperCase());
                }
                final Map<Object, Duijie_ShiJieShouData> landInfoData_ids_final = landInfoData_ids_22;

//                jt.batchUpdate(sql, getBatchPreparedStatementSetter(insert_parameters_final, key, mainDeploy_tableId_final, landInfoData_ids_final));
                final List<Map<String, Object>> parameters = insert_parameters_final.get(key.toUpperCase());
                Duijie_ShiJieShouDeploy deploy1 = deploys_type.get(key);
                commonMapper.insertData(deploy1.getTableName(), parameters);

                //20220711 插入主表后获取主表ID
                if(mainDeploy_tableId == null && mainDeploy != null && StringUtils.equalsIgnoreCase(key, mainDeploy.getType()) && parameters.size() == 1) {
                    Integer tmpShiDataId = getShiDataId(mainDeploy, mainDeploy_mainKey_sql, mainDeploy_mainKey_parameters);
                    if (tmpShiDataId != null) {
                        mainDeploy_tableId = tmpShiDataId;
                    }
                }


                //20220711
                //获取表中的记录ID
                Duijie_ShiJieShouDeploy deploy = deploys_type.get(key.toUpperCase());
                if(StringUtils.equalsIgnoreCase(deploy.getType(), "land_info") || StringUtils.equalsIgnoreCase(deploy.getType(), "quotation") || StringUtils.equalsIgnoreCase(deploy.getType(), "jyq_quotation")) {

                        Map<Object, Duijie_ShiJieShouData> data_ids = getZbfDataIds(clientIdColumnName, deploy, mainKey_sql_map.get(deploy.getType().toUpperCase()),
                                mainKey_parameters_string_map.get(deploy.getType().toUpperCase()), mainKey_parameters_map.get(deploy.getType().toUpperCase()), "zbfSourceId");

                        xmbm_dataIds.put(deploy.getType().toUpperCase(), data_ids);
                }
            }



            //修改update
            for(final String key: update_sql.keySet()) {
                sql = update_sql.get(key);


                final Integer mainDeploy_tableId_final = mainDeploy_tableId;
                //待交易资产的数据
                Map<Object, Duijie_ShiJieShouData> landInfoData_ids_22 = landInfoData_ids;
                if(xmbm_dataIds.get("land_info".toUpperCase()) != null) {
                    landInfoData_ids_22 = xmbm_dataIds.get("land_info".toUpperCase());
                }
                final Map<Object, Duijie_ShiJieShouData> landInfoData_ids_final = landInfoData_ids_22;

                // TODO 待改成批量插入的形式 现在是for循环一条条执行

//                jt.batchUpdate(sql, getBatchPreparedStatementSetter(insert_parameters_final, key, mainDeploy_tableId_final, landInfoData_ids_final));
                final List<Map<String, Object>> parameters = update_parameters_final.get(key.toUpperCase());
                Duijie_ShiJieShouDeploy deploy1 = deploys_type.get(key);
                for (Map<String, Object> parameter : parameters) {
                    commonMapper.updateData(deploy1.getTableName(), parameter);
                }


                // 20220713 hxj 修改的情况也要重复获取一次表中的记录ID，否则land_info的zbfId会被改回区平台的id
                //20220711
                //获取表中的记录ID
                Duijie_ShiJieShouDeploy deploy = deploys_type.get(key.toUpperCase());
                if(StringUtils.equalsIgnoreCase(deploy.getType(), "land_info") || StringUtils.equalsIgnoreCase(deploy.getType(), "quotation") || StringUtils.equalsIgnoreCase(deploy.getType(), "jyq_quotation")) {
                    Map<Object, Duijie_ShiJieShouData> data_ids = getZbfDataIds(clientIdColumnName, deploy, mainKey_sql_map.get(deploy.getType().toUpperCase()),
                            mainKey_parameters_string_map.get(deploy.getType().toUpperCase()), mainKey_parameters_map.get(deploy.getType().toUpperCase()), "zbfSourceId");

                    xmbm_dataIds.put(deploy.getType().toUpperCase(), data_ids);
                }
            }

            //20220711 更新landInfo的ZBFID
//                        if(insert_sql.get("quotation".toUpperCase()) != null) {
            if(mainDeploy != null && (StringUtils.equalsIgnoreCase(mainDeploy.getXmbm(), "proj") || StringUtils.equalsIgnoreCase(mainDeploy.getXmbm(), "transactionsOther"))) {
                updateZbfId(landInfoData_ids, quotationData_ids, xmbm_dataIds);
            }

            //删除delete
            deleteData_FromInsertOrUpdate(delete_sql, delete_parameters_final, deploys_type);

//
//            //提交事务
//            transactionManager.commit(status);
//        } catch (Exception e) {
//            if (roleInfo == null) {
//                if (e instanceof SQLException || e instanceof BadSqlGrammarException) {
//                    roleInfo = new RoleInfo("接收端出现SQL语法错误！请联系管理员查看日志！");
//                } else {
//                    roleInfo = new RoleInfo(e.getMessage());
//                }
//            }
//            logger.error(log_head + " insertOrUpdate 异常", e);
//
//            try {
//                transactionManager.rollback(status);
//            } catch (Exception e1) {
//            }
//
//            //throw e;
//            isException = true;
//            exception = e;
//        }
    }

    public void updateZbfId(Map<Object, Duijie_ShiJieShouData> landInfoData_ids, Map<Object, Duijie_ShiJieShouData> quotationData_ids, Map<String, Map<Object, Duijie_ShiJieShouData>> xmbm_dataIds) {
        String sql;
//        if(mainDeploy != null && StringUtils.equalsIgnoreCase(mainDeploy.getXmbm(), "proj")) {
        //待交易资产的数据
        Map<Object, Duijie_ShiJieShouData> landInfoData_ids_22 = landInfoData_ids;
        if(xmbm_dataIds.get("land_info".toUpperCase()) != null) {
            landInfoData_ids_22 = xmbm_dataIds.get("land_info".toUpperCase());
        }
        final Map<Object, Duijie_ShiJieShouData> landInfoData_ids_final = landInfoData_ids_22;

        //报价的数据
        Map<Object, Duijie_ShiJieShouData> quotationData_ids_22 = quotationData_ids;
        if(xmbm_dataIds.get("quotation".toUpperCase()) != null) {
            quotationData_ids_22 = xmbm_dataIds.get("quotation".toUpperCase());
        }
        if(xmbm_dataIds.get("jyq_quotation".toUpperCase()) != null) {
            quotationData_ids_22 = xmbm_dataIds.get("jyq_quotation".toUpperCase());
        }
        final Map<Object, Duijie_ShiJieShouData> quotationData_ids_final = quotationData_ids_22;


        List<List<Integer>> zb = new ArrayList<List<Integer>>();
        for(Object landInfo_clientId: landInfoData_ids_final.keySet()) {
            Duijie_ShiJieShouData shiData_landInfo = landInfoData_ids_final.get(landInfo_clientId);

            if(shiData_landInfo._clientZbfId != null && !StringUtils.equals(shiData_landInfo._clientZbfId.toString(), "0")) {
                Duijie_ShiJieShouData shiData_quotation = quotationData_ids_final.get(shiData_landInfo._clientZbfId);
                if(shiData_quotation != null) {
                    List<Integer> zb_sub = new ArrayList<Integer>();

                    zb_sub.add(shiData_quotation.shiId);
                    zb_sub.add(shiData_landInfo.shiId);

                    zb.add(zb_sub);
                }
            }
        }
        if(zb.size() > 0) {
            final List<List<Integer>> zb_final = zb;
            // TODO
//            sql = "UPDATE land_info SET zbfId=? WHERE id=?";
//            jt.batchUpdate(sql, new BatchPreparedStatementSetter() {
//                @Override
//                public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
//                    List<Integer> zb_sub = zb_final.get(i);
//
//                    Integer new_zbfId = zb_sub.get(0);
//                    Integer id = zb_sub.get(1);
//
//                    preparedStatement.setInt(1, new_zbfId);
//                    preparedStatement.setInt(2, id);
//                }
//
//                @Override
//                public int getBatchSize() {
//                    return zb_final.size();
//                }
//            });
        }
//        }
    }

    public void deleteData_FromInsertOrUpdate(Map<String, String> delete_sql, final Map<String, List<List<Object>>> delete_parameters_final, Map<String, Duijie_ShiJieShouDeploy> deploys_map_final) {
        String sql;
        for(final String key: delete_sql.keySet()) {
            sql = delete_sql.get(key);
//            jt.batchUpdate(sql, new BatchPreparedStatementSetter() {
//                @Override
//                public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
////                                    Duijie_ShiJieShouDeploy deploy = deploys_map_final.get(key.toUpperCase());
//
//                    List<Object> parameters = delete_parameters_final.get(key.toUpperCase()).get(i);
//
//                    int jj=0;
//                    for(Object o : parameters) {
//                        jj++;
//                        preparedStatement.setObject(jj, o);
//                    }
//                }
//
//                @Override
//                public int getBatchSize() {
//                    return delete_parameters_final.get(key.toUpperCase()).size();
//                }
//            });
            Duijie_ShiJieShouDeploy deploy = deploys_map_final.get(key.toUpperCase());
            List<List<Object>> parameters = delete_parameters_final.get(key.toUpperCase());
            List<Integer> ids = ListUtil.toSingleList(parameters, param -> (Integer) param.get(0));
            commonMapper.deleteDataByIds(deploy.getTableName(), ids, deploy.getIdColumn());
        }
    }

}
